package com.ywb.ex09.dao.impl;

import com.ywb.ex09.dao.service.ServiceDao;
import com.ywb.ex09.model.PetItem;
import com.ywb.ex09.tools.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ServiceDaoImpl implements ServiceDao {
    Connection conn;
    PreparedStatement ps;
    Statement st;
    ResultSet rs;
    @Override
    public List<PetItem> queryAllData() {
        ResultSet rs=null;
        ArrayList<PetItem> list=new ArrayList<PetItem>();
        try{
            conn= JDBCUtils.getConnection();
            st=conn.createStatement();
            String sql="select * from pet";
            rs=st.executeQuery(sql);
            while(rs.next()){
                PetItem petItem=new PetItem();
                petItem.setId(rs.getString("id"));
                petItem.setPetname(rs.getString("petname"));
                petItem.setPrice(rs.getDouble("price"));
                petItem.setUnit(rs.getString("unit"));
                list.add(petItem);
            }
            return list;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs,st,conn);
        }
        return null;
    }

    @Override
    public void addPetItem(PetItem petItem) {
        try{
            conn= JDBCUtils.getConnection();
            st=conn.createStatement();
            String sql="insert into pet(id,petname,price,unit) values('"+petItem.getId()+"','"
                    +petItem.getPetname()+"','"+petItem.getPrice()+"','"+petItem.getUnit()+"')";
            int num=st.executeUpdate(sql);
            if(num>0){
                System.out.println("插入数据成功");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs,st,conn);
        }
    }

    @Override
    public boolean addPetItemforFlag(PetItem petItem) {
        boolean result = false;
        try{
            conn= JDBCUtils.getConnection();
            st=conn.createStatement();
            String sql="insert into pet(id,petname,price,unit) values('"+petItem.getId()+"','"
                    +petItem.getPetname()+"','"+petItem.getPrice()+"','"+petItem.getUnit()+"')";
            int num=st.executeUpdate(sql);
            if(num>0){
                System.out.println("插入数据成功");
                result = true;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs,st,conn);
        }
        return result;
    }

    @Override
    public void deletePetItem(String id) {
        try{
            String sql="delete FROM pet WHERE id = ? ";
            ps = JDBCUtils.getConnection().prepareCall(sql);
            ps.setInt(1, Integer.parseInt(id));
            int num=ps.executeUpdate();
            if(num>0){
                System.out.println("删除数据成功");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs,st,conn);
        }
    }

    @Override
    public boolean deletePetItemforFlag(String id) {
        boolean result = false;
        try{
            String sql="delete FROM pet WHERE id = ? ";
            ps = JDBCUtils.getConnection().prepareCall(sql);
            ps.setInt(1, Integer.parseInt(id));
            int num=ps.executeUpdate();
            if(num>0){
                System.out.println("删除数据成功");
                result = true;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs,st,conn);
        }
        return result;
    }

    @Override
    public void updatePetItem(PetItem petItem) {
        try{
            String sql = "update pet set petname = ?, price = ?, unit = ? where id = ?";
            ps = JDBCUtils.getConnection().prepareStatement(sql);
            ps.setString(1, petItem.getPetname());
            ps.setDouble(2, petItem.getPrice());
            ps.setString(3, petItem.getUnit());
            ps.setString(4, petItem.getId());

            int num=ps.executeUpdate();
            if(num>0){
                System.out.println("插入数据成功");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs,st,conn);
        }
    }

    @Override
    public boolean updatePetItemforFlag(PetItem petItem) {
        boolean result = false;
        try{
            String sql = "update pet set petname = ?, price = ?, unit = ? where id = ?";
            ps = JDBCUtils.getConnection().prepareStatement(sql);
            ps.setString(1, petItem.getPetname());
            ps.setDouble(2, petItem.getPrice());
            ps.setString(3, petItem.getUnit());
            ps.setString(4, petItem.getId());

            int num=ps.executeUpdate();
            if(num>0){
                System.out.println("插入数据成功");
                result = true;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs,st,conn);
        }
        return result;
    }
}
